﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Maticsoft.DBUtility;
using Aspose.Words;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Text;
using System.Collections.Specialized;
using System.Configuration;
using Aspose.Words.Rendering;
using Aspose.Words.Drawing;
using DQYT.HTDZ.Web;

namespace SkWeb.FAXZ
{
    public partial class WordBrowse : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            //string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            //string strJing_id = Request.QueryString["id"];//模板ID
           // string strName = Request.QueryString["name"];

            string op = Request.QueryString["op"];
            if (op == null)
                return;


            switch (op)
            {
                case "saveWordDoc"://保存并添加新方案时调用，把井id、模板id还有修改的word模板保存到数据库
                    insertNewScheme();
                    break;
                case  "browsefromDB": //从方案管理页面浏览时，传入井号和方案名
                    browseFromDB();
                    break;
                case "browseFromLFS"://当方案没有生成时，模板从服务器文件系统加载
                    browseFromLFS();
                    break;
                case "download"://下载word方案时调用，传入井号和方案名
                    downloadDoc();
                    break;
                case "sh":
                    browseSh();//审核方案时调用
                    break;
               
            }

        }

        private void browseSh()
        {
            string strFaId = Request.QueryString["id"].Trim();//设计完成之后的方案id
            string strSql = "select jing_id,tmp_id from tb_fa where id="+strFaId;
            DataTable dt = OracleHelper.Query(strSql).Tables[0];

            string strJing_id = dt.Rows[0]["JING_ID"].ToString().Trim();//井名
            string strTemplate_id = dt.Rows[0]["TMP_ID"].ToString().Trim();//模板名

            MemoryStream ms = ReadBlob(Convert.ToInt32(strJing_id), "jing_id", "word_doc", "tb_fa");
            Document tempDocument = new Aspose.Words.Document(ms);

            string strFileName = genWordDoc(strJing_id, strTemplate_id, tempDocument);
            Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&name=" + Server.UrlEncode(strFileName));
            return;
        }

        private void downloadDoc()
        {
            string strJingName = Request.QueryString["jingName"].Trim();//井名
            string strTmpName = Request.QueryString["tmpName"].Trim();//模板名

            string strSql = "select id from skgysjb where trim(jh)='" + strJingName + "'";
            string strJing_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

            strSql = "select tmp_id from tb_template_menu where tmp_mc='" + strTmpName + "'";
            string strTemplate_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();
            MemoryStream ms = ReadBlob(Convert.ToInt32(strJing_id), "jing_id", "word_doc", "tb_fa");
            Document tempDocument = new Aspose.Words.Document(ms);
            string strPath = Server.MapPath("~/tempDoc/") +genWordDoc(strJing_id, strTemplate_id, tempDocument);

            Response.ContentType = "application/msword";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(strJingName) + "_" + Server.UrlEncode(strTmpName) + ".doc");
            Response.TransmitFile(strPath);
            
           
        }

        /// <summary>
        /// 从数据库加载模板生成文档
        /// </summary>
        private void browseFromDB()
        {
            string strJingName = Request.QueryString["jingName"].Trim();//井名
            string strTmpName = Request.QueryString["tmpName"].Trim();//模板名

            string strSql = "select id from skgysjb where trim(jh)='" + strJingName + "'";
            string strJing_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();
            
            strSql = "select tmp_id from tb_template_menu where tmp_mc='" + strTmpName + "'";
            string strTemplate_id = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();

            MemoryStream ms = ReadBlob(Convert.ToInt32(strJing_id), "jing_id", "word_doc", "tb_fa");
            Document  tempDocument = new Aspose.Words.Document(ms);
           
            string strFileName = genWordDoc(strJing_id, strTemplate_id,tempDocument);
            Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&name=" + Server.UrlEncode(strFileName));
            return;
        }

        /// <summary>
        /// 从头本地文件系统提取模板生成文档
        /// </summary>
        private void browseFromLFS()
        {
            string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            string strJing_id = Request.QueryString["id"];//模板ID
            string strSql = "select tmp_url from tb_template_menu where tmp_id =" + strTemplate_id;
            string docPath = OracleHelper.Query(strSql).Tables[0].Rows[0][0].ToString();
            docPath = Server.MapPath("~/") + docPath;
            Aspose.Words.Document tempDocument;
            tempDocument = new Aspose.Words.Document(docPath);
            string strFileName = genWordDoc(strJing_id, strTemplate_id, tempDocument);
            Response.Redirect("WordBrowse.aspx?tempalteid=" + strTemplate_id + "&id=" + strJing_id + "&name=" + Server.UrlEncode(strFileName));
            return;

        }

        public string genWordDoc(string strJingId, string strTemplateId,Document tempDocument)
        {
            DocumentBuilder builder;
            builder = new DocumentBuilder(tempDocument);
            string strSql = "select * from tb_bookmark where faid =" + strTemplateId;
            DataTable dtBM = OracleHelper.Query(strSql).Tables[0];
            DataColumn dc = new DataColumn("VALUE", Type.GetType("System.String"));
            dtBM.Columns.Add(dc);
            FillTable(dtBM, strJingId);

            foreach (DataRow dr in dtBM.Rows)
            {
                tempDocument.Range.Bookmarks[dr["NAME"].ToString()].Text = dr["Value"].ToString().Trim();
            }

            ////下面的代码是针对 新井-压力 方案写死的代码，如有其它方案需要遵守 新井-压力 的 命名规则

            if (tempDocument.Range.Bookmarks["审核日期1"].Text.Trim().Length < 1)
            {
                tempDocument.Range.Bookmarks["审核日期1"].Text = "    年   月    日";
            }
            if (tempDocument.Range.Bookmarks["审核日期2"].Text.Trim().Length < 1)
            {
                tempDocument.Range.Bookmarks["审核日期2"].Text = "    年   月    日";
            }


            if (tempDocument.Range.Bookmarks["设计日期"].Text.Trim().Length < 1)
            {
                tempDocument.Range.Bookmarks["设计日期"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
                tempDocument.Range.Bookmarks["设计日期1"].Text = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";

            }
            else
            {
                strSql = "select sjrq from tb_fa where jing_id=" + strJingId;
                DateTime time = DateTime.Parse(tempDocument.Range.Bookmarks["设计日期"].Text.Trim());
                tempDocument.Range.Bookmarks["设计日期"].Text = time.Year.ToString() + "年" + time.Month.ToString() + "月" + time.Day.ToString() + "日";//time.ToString("yyyy年mm月dd日");
                tempDocument.Range.Bookmarks["设计日期1"].Text = time.Year.ToString() + "年" + time.Month.ToString() + "月" + time.Day.ToString() + "日";
            }
            HttpCookie cookie = Request.Cookies["user"];
            string strSjr = Server.UrlDecode(cookie.Value).Replace("name=", "");


            //tempDocument.Range.Bookmarks["设计人"].Text = strSjr;
            //插入图片的书签，要以插入->书签 的方式加入
            Shape shape = new Shape(tempDocument, ShapeType.Image);
            shape.ImageData.SetImage(Server.MapPath("~/Files/autograph/" + strSjr + ".png"));
            // shape.ImageData.SetImage(Server.MapPath("~/Files/autograph/a.jpg"));
            // System.Drawing.Bitmap img = new System.Drawing.Bitmap(Server.MapPath("~/Files/autograph/image013.png"));
            shape.Width = 350;
            shape.Height = 32;
            shape.HorizontalAlignment = HorizontalAlignment.Right; //

            builder.MoveToBookmark("pic");
            builder.InsertNode(shape);
           

            if (tempDocument.Range.Bookmarks["垂直深度"] != null)
                tempDocument.Range.Bookmarks["垂直深度"].Text = tempDocument.Range.Bookmarks["射孔井段2"].Text;//h


            if (tempDocument.Range.Bookmarks["定位短节"] != null && tempDocument.Range.Bookmarks["炮头长度"] != null)
                tempDocument.Range.Bookmarks["定位短节"].Text = (Convert.ToSingle(tempDocument.Range.Bookmarks["射孔井段2"].Text) - Convert.ToSingle(tempDocument.Range.Bookmarks["炮头长度"].Text)).ToString();//射孔井段
            //销钉计算
            if (tempDocument.Range.Bookmarks["ρ"] != null && tempDocument.Range.Bookmarks["静液柱压力"] != null&&tempDocument.Range.Bookmarks["温度"]!=null)
            {
                float h = Convert.ToSingle(tempDocument.Range.Bookmarks["射孔井段2"].Text.Trim());
                tempDocument.Range.Bookmarks["ρ"].Text = tempDocument.Range.Bookmarks["完井液密度"].Text;//ρ
                float p = Convert.ToSingle(tempDocument.Range.Bookmarks["完井液密度"].Text.Trim());
                double pgh = p * h * 0.0098;
                tempDocument.Range.Bookmarks["静液柱压力"].Text = String.Format("{0:0.00}", pgh); //ρgh

                int t = (int)(h * 0.035 + 1);//温度
                tempDocument.Range.Bookmarks["温度"].Text = t.ToString();

                //百分率
                string strPerSql = "select tem_value from tb_temperature where tem= " + t.ToString();
                float percent = Convert.ToSingle(OracleHelper.Query(strPerSql).Tables[0].Rows[0][0]);
                tempDocument.Range.Bookmarks["百分率"].Text = percent.ToString();

                float jqz = Convert.ToSingle(tempDocument.Range.Bookmarks["剪切值"].Text.Trim());//常温下的剪切值
                double Pmax = jqz * 1.045 * (1 - percent / 100);
                tempDocument.Range.Bookmarks["Pmax"].Text = String.Format("{0:0.00}", Pmax);

                double Pmin = jqz * 0.955 * (1 - percent / 100);
                tempDocument.Range.Bookmarks["Pmin"].Text = String.Format("{0:0.00}", Pmin);

                float safe_pre = Convert.ToSingle(tempDocument.Range.Bookmarks["安全压力"].Text.Trim());
                double n = (pgh + safe_pre) / Pmin;
                tempDocument.Range.Bookmarks["n"].Text = String.Format("{0:0.00}", n);
                int N = (int)(n + 1);
                tempDocument.Range.Bookmarks["m"].Text = N.ToString();

                //E369*H365-F360
                double Ps = N * Pmax - pgh;
                double Px = N * Pmin - pgh;
                tempDocument.Range.Bookmarks["Ps"].Text = String.Format("{0:0.00}", Ps);//Ps
                tempDocument.Range.Bookmarks["Px"].Text = String.Format("{0:0.00}", Px);//Ps
            }

            string strQx = "102";//在数据为空时设置102
            string strQxBm = tempDocument.Range.Bookmarks["枪型"].Text;
            //提取枪型里面的数字
            if (!string.IsNullOrEmpty(strQxBm))
            {
                strQx = "";
                for (int i = 0; i < strQxBm.Length; i++)
                {
                    if (Char.IsNumber(strQxBm[i]))
                        strQx += strQxBm[i];
                }
            }

            string strYggg = "73";
            string strYgggBm = tempDocument.Range.Bookmarks["油管规格"].Text;
            if (!string.IsNullOrEmpty(strYgggBm))
            {
                strYggg = "";
                for (int i = 0; i < strYgggBm.Length; i++)
                {
                    if (Char.IsNumber(strYgggBm[i]))
                        strYggg += strYgggBm[i];
                    else if (strYgggBm[i] == '*')
                        break;
                }
                strYggg = String.Format("{0:0}", (int)(Convert.ToSingle(strYggg) + 0.5));
            }

            tempDocument.Range.Bookmarks["起爆装置接头"].Text = strQx;//Ps
            tempDocument.Range.Bookmarks["枪尾"].Text = strQx;//Ps
            tempDocument.Range.Bookmarks["筛管"].Text = strYggg;//Ps
            tempDocument.Range.Bookmarks["油管接箍"].Text = strYggg;//Ps
            tempDocument.Range.Bookmarks["油管短节"].Text = strYggg;//Ps

            string strPath = Server.MapPath("~/tempDoc/") + DateTime.Now.ToString("MM_dd_hh_mm_ss") + ".doc";
            tempDocument.Save(strPath, SaveFormat.Doc);
            FileInfo info = new FileInfo(strPath);
            return info.Name;

        }

        void FillTable(DataTable dt, string pkValue)
        {
            foreach (DataRow dr in dt.Rows)
            {
                string strFormula = dr["FORMULA"].ToString();
                if (string.IsNullOrEmpty(strFormula.Trim()))
                {
                    string name = dr["NAME"].ToString();
                    string text = dr["TEXT"].ToString();
                    string tablename = dr["TABLENAME"].ToString();
                    string fieldname = dr["FIELDNAME"].ToString();
                    string pkfield = dr["PKFIELD"].ToString();
                    string strSql = "select " + fieldname + " from " + tablename + " where " + pkfield + "='" + pkValue + "' ";
                    DataTable tempDT = OracleHelper.Query(strSql).Tables[0];
                    string strValue = " ";
                    if(tempDT.Rows.Count>0)
                    strValue = Convert.IsDBNull(tempDT.Rows[0][0]) ? "" : tempDT.Rows[0][0].ToString();
                    
                    dr["VALUE"] = strValue;
                }
                else
                {
                    DataTable dtFormula = OracleHelper.Query("select * from tb_formula where name='" + strFormula + "' ").Tables[0];
                    string pktable = dtFormula.Rows[0]["PKTABLENAME"].ToString();
                    string pkfield = dtFormula.Rows[0]["PKFIELDNAME"].ToString();
                    string Content = dtFormula.Rows[0]["VALUE"].ToString();

                    DataTable dtVars = OracleHelper.Query("select * from tb_formula_var where name ='" + strFormula + "' ").Tables[0];
                    foreach (DataRow drVar in dtVars.Rows)
                    {
                        string varName = drVar["BKNAME"].ToString();
                        string strTable = drVar["TABLENAME"].ToString();
                        string strField = drVar["FIELDNAME"].ToString();
                        string strpkField = drVar["PKFIELDNAME"].ToString();
                        string strVarValue = OracleHelper.Query(" select " + strField + " from " + strTable + " where " + strpkField + "='" + pkValue + "' ").Tables[0].Rows[0][0].ToString();
                        Content = Content.Replace(varName, strVarValue);
                    }
                    CodeDriver driver = new CodeDriver();
                    bool isError;
                    string strResult = driver.ComplileAndRun(Content, out isError);//调用执行代码的类
                    dr["VALUE"] = strResult;
                }
            }
            dt.AcceptChanges();

        }


        public string insertNewScheme()
        {
            try
            {
                string uploadpath = Server.MapPath(@"~/FileUpLoad/");
                if (!System.IO.Directory.Exists(uploadpath))
                {
                    System.IO.Directory.CreateDirectory(uploadpath);
                }
                System.Web.HttpFileCollection filesupload = Request.Files;
                System.Web.HttpPostedFile file;
                string key = filesupload.GetKey(0).ToLower();
                int i = 0;
                while (i <= filesupload.Count && "editfile" != key)
                {
                    i++;
                    key = filesupload.GetKey(i).ToString();
                }
                if ("editfile" == key)
                {
                    file = filesupload[i];

                    string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
                    string strJing_id = Request.QueryString["id"];//模板ID
                    // string filePath = Request.QueryString["name"];
                    if (file.ContentLength > 1)
                    {
                        byte[] wordDoc = new byte[file.ContentLength];
                        file.InputStream.Read(wordDoc, 0, (int)file.ContentLength);

                        string strSql = "select * from  tb_fa  where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;
                        DataTable dt = OracleHelper.Query(strSql).Tables[0];
                        if (dt.Rows.Count > 0)
                        {
                            strSql = "update tb_fa set word_doc =:word_doc where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;
                            OracleParameter p = new OracleParameter(":word_doc", OracleType.Blob);
                            p.Value = wordDoc;
                            OracleParameter[] op = { p };
                            OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

                        }
                        else
                        {
                            strSql = "insert into tb_fa(jing_id,tmp_id,sjr,sjrq,word_doc,sjr_code) values (:jing_id,:tmp_id,:sjr,:sjrq,:word_doc,:sjr_code )";

                            OracleParameter p =  new OracleParameter(":jing_id", OracleType.Number);
                            OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                            OracleParameter p2 = new OracleParameter(":sjr", OracleType.VarChar);
                            OracleParameter p3 = new OracleParameter(":sjrq", OracleType.DateTime);
                            OracleParameter p4 = new OracleParameter(":word_doc", OracleType.Blob);
                            OracleParameter p5 = new OracleParameter(":sjr_code", OracleType.VarChar);
                            HttpCookie cookie = Request.Cookies["user"];
                            p.Value = Convert.ToInt32(strJing_id);
                            p1.Value = Convert.ToInt32(strTemplate_id);
                            p2.Value = Server.UrlDecode(cookie.Value).Replace("name=","");
                            p3.Value = DateTime.Now;
                            p4.Value = wordDoc;
                            p5.Value = Globals.CurrentUserInfo.USE_CODE;
                            OracleParameter[] op = { p, p1, p2,p3,p4,p5 };
                            OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

                        }

                    }



                    return "操作成功！";
                }
                else
                {
                    return "尚未找到操作的文件！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message.ToString();
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string strTemplate_id = Request.QueryString["tempalteid"];//模板ID
            string strJing_id = Request.QueryString["id"];//模板ID
            string filePath = Request.QueryString["name"];
            if (filePath != null)
            {
                filePath = Server.MapPath("~/")+"tempDoc\\" + filePath;
                FileStream stream = new FileStream(filePath,FileMode.Open);
                byte[] wordDoc = new byte[stream.Length];
                stream.Read(wordDoc,0,(int)stream.Length);

                string strSql = "select * from  tb_fa  where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;
                DataTable dt = OracleHelper.Query(strSql).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    strSql = "update tb_fa set word_doc =:word_doc where JING_ID=" + strJing_id + " and tmp_id= " + strTemplate_id;

                    OracleParameter p = new OracleParameter(":word_doc", OracleType.Blob);
                    p.Value = wordDoc;
                    OracleParameter[] op = { p };
                    OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

                }
                else
                {
                        strSql = "insert into tb_fa(jing_id,tmp_id,word_doc) values (:jing_id,:tmp_id,:word_doc )";


                        OracleParameter p = new OracleParameter(":jing_id", OracleType.Number);
                        OracleParameter p1 = new OracleParameter(":tmp_id", OracleType.Number);
                        OracleParameter p2 = new OracleParameter(":word_doc", OracleType.Blob);

                        p.Value = Convert.ToInt32(strJing_id);
                        p1.Value = Convert.ToInt32(strTemplate_id);
                        p2.Value = wordDoc;
                        OracleParameter[] op = { p,p1,p2 };
                        OracleHelper.ExecuteNonQuery(CommandType.Text, strSql, op);

                  }

            }
        }

        /// <summary>
        /// 读取 Oracle Blob 到文件中。
        /// </summary>
        /// <param name="idData">id 值</param>
        /// <param name="fileName">文件名</param>
        /// <param name="id">id 键</param>
        /// <param name="blob">blob 键</param>
        /// <param name="tableName">表名</param>
        public MemoryStream ReadBlob(int idData, string id, string blob, string tableName)
        {
            OracleConnection conn;
            string connString = ConfigurationManager.AppSettings["ConnectionString"];
            using (conn = new OracleConnection(connString))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();

                    // 利用事务处理（必须）
                    OracleTransaction transaction = cmd.Connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    // 获得 OracleLob 指针
                    string sql = "select " + blob + " from " + tableName + " where " + id + " = " + idData;
                    cmd.CommandText = sql;
                    OracleDataReader dr = cmd.ExecuteReader();
                    dr.Read();
                    OracleLob tempLob = dr.GetOracleLob(0);
                    dr.Close();

                    // 读取 BLOB 中数据，写入到文件中
                    MemoryStream fs = new MemoryStream();
                    //FileStream fs = new FileStream(fileName, FileMode.Create);
                    int length = 1048576;
                    byte[] Buffer = new byte[length];
                    int i;
                    while ((i = tempLob.Read(Buffer, 0, length)) > 0)
                    {
                        fs.Write(Buffer, 0, i);
                    }
                   // fs.Close();
                    tempLob.Clone();
                    cmd.Parameters.Clear();

                    // 提交事务
                    transaction.Commit();
                    return fs;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }



    }




   
}